/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBLayer;

import ModelLayer.*;
import java.util.ArrayList;
import java.sql.*;

/**
 *
 * @author Claus
 */
public class DBSalesLineItem implements IFDBSalesLineItem
{


	private Connection con;

	public DBSalesLineItem()
	{
		con = DBConnection.getInstance().getDBcon();
	}

	public ArrayList<SalesLineItem> getSalesOrderItems(int salesID)
	{
		return miscWhere("salesID = " + salesID, true);
	}

	public int updateSalesLineItem(SalesLineItem salesLineItem)
	{
		SalesLineItem item = salesLineItem;

		int rc = -1;

		String query = "UPDATE SalesLineItem SET "
				+ "salesID =" + item.getOrder().getSalesID() + ", "
				+ "quantity =" + item.getQuantity() + ", "
				+ "barcode ='" + item.getProduct().getBarCode() + "', "
				+ " WHERE sliID = " + item.getSliID();

		System.out.println("Update query:" + query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}
		catch (Exception ex)
		{
			System.out.println("Update exception in saleslineitem db: " + ex);
		}
		return (rc);
	}

	public int insertSalesLineItem(SalesLineItem item)
	{
		int nextID = GetMax.getMaxId("Select max(sliID) from SalesLineItem");
		nextID = nextID + 1;
		System.out.println("next salesID = " + nextID);

		int rc = -1;
		String query = "INSERT INTO saleslineitem(sliID, quantity, barcode, salesID)  VALUES("
				+ nextID + ","
				+ item.getQuantity() + ",'"
				+ item.getProduct().getBarCode() + "',"
				+ item.getOrder().getSalesID() + ")";
		
		System.out.println("insert : " + query);

		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}
		catch (SQLException ex)
		{
			System.out.println("SalesLineItem ikke oprettet");
			System.out.println("Insert exception in saleslineitem db: " + ex);
			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return (rc);
	}

	public int deleteSalesLineItem(int sliID)
	{
		DBProduct dbProduct = new DBProduct();
		SalesLineItem sli = singleWhere("sliID =" + sliID, true);
		dbProduct.increaseCurrentStock(sli.getProduct().getBarCode(), sli.getQuantity());
		int rc = -1;

		String query = "DELETE FROM SalesLineItem WHERE sliID = "
				+ sliID;

		System.out.println(query);

		try
		{
			con.setAutoCommit(false);
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			con.commit();
			con.setAutoCommit(true);
		}
		catch (Exception ex)
		{
			System.out.println("Delete exception in saleslineitem db: " + ex);

			try
			{
				rc = -1;
				con.rollback();
				con.setAutoCommit(true);
			}
			catch (Exception ex1)
			{
				System.out.println("Error rolling back" + ex1);
			}
		}
		return (rc);
	}

	private ArrayList<SalesLineItem> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<SalesLineItem> list = new ArrayList<SalesLineItem>();
		String query = buildQuery(wClause);

		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next())
			{
				SalesLineItem salesLineItem = new SalesLineItem();
				salesLineItem = buildSalesLineItem(results);
				list.add(salesLineItem);

				if (retrieveAssociation)
				{
					IFDBSalesOrder dbSale = new DBSalesOrder();
					IFDBProduct dbProduct = new DBProduct();
					salesLineItem.setProduct(dbProduct.findProduct(results.getString("barcode"), false));
					//salesLineItem.setOrder(dbSale.findSalesOrder(results.getInt("salesID"), false));
				
				}
			}

			stmt.close();

		}
		catch (Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	private SalesLineItem singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesLineItem salesLineItem = new SalesLineItem();

		String query = buildQuery(wClause);
		System.out.println(query);
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next())
			{
				salesLineItem = buildSalesLineItem(results);

				if (retrieveAssociation)
				{
					
					IFDBProduct dbProduct = new DBProduct();
					salesLineItem.setProduct(dbProduct.findProduct(results.getString("barcode"), retrieveAssociation));
					IFDBSalesOrder dbSale = new DBSalesOrder();
					//salesLineItem.setOrder(dbSale.findSalesOrder(results.getInt("salesID"), false));
				}

				stmt.close();

			}
			else
			{
				salesLineItem = null;
			}
		}
		catch (Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return salesLineItem;
	}

	private String buildQuery(String wClause)
	{
		String query = "SELECT sliID, quantity, barcode, salesID FROM SalesLineItem";

		if (wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}

		return query;
	}

	private SalesLineItem buildSalesLineItem(ResultSet results)
	{
		SalesLineItem salesLineItem = new SalesLineItem();

		try
		{
			salesLineItem.setSliID(results.getInt("sliID"));
			salesLineItem.setQuantity(results.getInt("quantity"));
		}
		catch (Exception e)
		{
			System.out.println("error in building the salesOrder object");
		}
		return salesLineItem;
	}
}
